﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Xml;
namespace youdaoImport
{
    class Program
    {
        //生词表
        static string datasource_Newword = "Data Source=newword.sqlite";
        //主表
        static string datasource_Words = "Data Source=wordinfo.sqlite";
        //有道导出文件
        static string filename = "aaa.xml";
        static void Main(string[] args)
        {
            //导入有道词典xml
            XmlDocument doc = new XmlDocument();
            doc.Load(filename);
            var wordItems=WordItem.getWordItems(doc);

                //判断每个单词在生词表是不是存在
                //不存在则标记为false,存在标记为true
                //判断每个单词在总表中是不是存在，
                //存在则设置dictwordid值
            List<miracle> miracleWords = OperateSqlite(wordItems);
            //批量插入sqlite数据表newword 

            int words=importMiracleNewWords(miracleWords);
            Console.WriteLine("导入完毕，共导入{0}条单词。", words);
            Console.Read();
        }

        private static int importMiracleNewWords(List<miracle> miracleWords)
        {
            List<string> sqllist = getsqlList(miracleWords);
            
                int iResult = sqliteEngineer.ExecuteBySqlList(datasource_Newword, sqllist);
            return iResult;
            
        }
        /// <summary>
        /// 拼接批量插入语句
        /// </summary>
        /// <param name="miracleWords"></param>
        /// <returns></returns>
        private static List<string> getsqlList(List<miracle> miracleWords)
        {
            List<string> sqllist = new List<string>();
            string insertSql = "insert into newword(english,chinese,dj,kk,addDate,voiceFlag,dictWordId ,categoryId ,reciteFlag) values('{0}','{1}','{2}','{3}',date('now'),{4},{5},{6},{7})";
            foreach (miracle m in miracleWords)
            {
                if (!m.isExist)
                {
                    string strtemp = string.Format(insertSql, m.english, m.chinese.Replace("'", "''"), m.dj.Replace("'", "''"), m.kk.Replace("'", "''"), m.voiceflag, m.dictwordid, m.categoryId, m.reciteFlag);
                    sqllist.Add(strtemp);
                }
            }
            return sqllist;
        }

        private static List<miracle> OperateSqlite(List<WordItem> wordItems)
        {
            List<miracle> miList = new List<miracle>();
            foreach (WordItem wi in wordItems)
            {
                miracle m = new miracle(wi);
                //判断该单词在单词表中是否存在，在则设置IsExist
                SetMiracleWordIfExistInNewWords(ref m);
                //判断每个单词在总表中是不是存在，
                //存在则设置dictwordid值
                SetMiracleWordIfExistInMajor(ref m);
                miList.Add(m);
            }
            return miList;
        }

        private static void SetMiracleWordIfExistInMajor(ref miracle m)
        {
            string strsql = "select distinct dict_word_id from wordinfo where english='" + m.english + "'";
            DataTable dt = sqliteEngineer.GetDateBySql(datasource_Words, strsql);
            if (dt == null) return;
            if (dt.Rows.Count == 0) return;
            if (dt.Rows[0][0] == null) return;
            if (string.IsNullOrEmpty(dt.Rows[0][0].ToString())) return;
            m.dictwordid = dt.Rows[0][0].ToString();
        }

        private static void SetMiracleWordIfExistInNewWords(ref miracle m)
        {
            string strsql = "select count(*) from newword where english='" + m.english + "'";
            DataTable dt = sqliteEngineer.GetDateBySql(datasource_Newword, strsql);
            int count = int.Parse(dt.Rows[0][0].ToString());
            if (count > 0)
            {
                m.isExist = true;
            }
        }
    }
}
